
Public Class User
    Inherits Police

    Protected _username As String
    Protected _user_pwd As String
    Protected _police_fullname As String
    Protected _police_age As Integer
    Private Shadows _Data As ArrayList

    Public Property police_fullname() As String
        Get
            Return _police_fullname
        End Get
        Set(ByVal value As String)
            _police_fullname = value
        End Set
    End Property

    Public Property police_age() As Integer
        Get
            Return _police_age
        End Get
        Set(ByVal value As Integer)
            _police_age = value
        End Set
    End Property

    Public Property username() As String
        Get
            Return _username
        End Get
        Set(ByVal value As String)
            _username = value
        End Set
    End Property

    Public Property user_pwd() As String
        Get
            Return _user_pwd
        End Get
        Set(ByVal value As String)
            _user_pwd = value
        End Set
    End Property

    Public Sub getUsername(ByVal inUsername As String)
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_Username"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inUsername", inUsername)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "tableUser")

        If objDs.Tables("tableUser").Rows.Count <> 0 Then
            Me.username = objDs.Tables("tableUser").Rows(0).Item("username").ToString()
        Else
            Me.username = ""
        End If
    End Sub

    Public Sub getUserPassword(ByVal inUsername As String)
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_UserPassword"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inUsername", inUsername)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "tableUser")

        If objDs.Tables("tableUser").Rows.Count <> 0 Then
            Me.user_pwd = objDs.Tables("tableUser").Rows(0).Item("user_pwd").ToString()
        Else
            Me.user_pwd = ""
        End If
    End Sub

    Public Sub getUsernameAndPassword(ByVal inUsername As String)
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_Username_UserPassword"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inUsername", inUsername)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "tableUser")

        If objDs.Tables("tableUser").Rows.Count <> 0 Then
            Me.username = objDs.Tables("tableUser").Rows(0).Item("username").ToString()
            Me.user_pwd = objDs.Tables("tableUser").Rows(0).Item("user_pwd").ToString()
        Else
            Me.username = ""
            Me.user_pwd = ""
        End If
    End Sub

    Public Function getUserDetails(ByVal inUsername) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_UserDetails"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inUsername", inUsername)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "tableUser")

        If objDs.Tables("tableUser").Rows.Count <> 0 Then
            Dim Obj As New User
            Obj.username = Utilities.ClearDBNullToMinValue(objDs.Tables("tableUser").Rows(0).Item("username").ToString(), TypeCode.String)
            Obj.prename.prename_id = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("prename_id").ToString(), TypeCode.Int32)
            Obj.prename.prename_short_tha = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("prename_short_tha").ToString(), TypeCode.String)
            Obj.prename.prename_short_eng = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("prename_short_eng").ToString(), TypeCode.String)
            Obj.prename.prename_full_tha = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("prename_full_tha").ToString(), TypeCode.String)
            Obj.prename.prename_full_eng = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("prename_full_eng").ToString(), TypeCode.String)
            Obj.name = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("name").ToString(), TypeCode.String)
            Obj.surname = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("surname").ToString(), TypeCode.String)
            Obj.gender = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("gender").ToString(), TypeCode.String)
            Obj.birth_date = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("birth_date"), TypeCode.DateTime)
            Obj.work_status.work_status_id = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("work_status_id").ToString(), TypeCode.Int32)
            Obj.work_status.work_status_name_tha = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("work_status_name_tha").ToString(), TypeCode.String)
            Obj.work_status.work_status_name_eng = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("work_status_name_eng").ToString(), TypeCode.String)
            Obj.organization.organization_id = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("organization_id").ToString(), TypeCode.String)
            Obj.organization.organization_name_tha = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("organization_name_tha").ToString(), TypeCode.String)
            Obj.organization.organization_name_eng = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("organization_name_eng").ToString(), TypeCode.String)
            Obj.organization.organization_parent_id = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("organization_parent_id").ToString(), TypeCode.String)
            Obj.organization.province.province_id = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("province_id").ToString(), TypeCode.String)
            Obj.organization.province.province_name_tha = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("province_name_tha").ToString(), TypeCode.String)
            Obj.organization.province.province_name_eng = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("province_name_eng").ToString(), TypeCode.String)
            Obj.commentary = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("commentary").ToString(), TypeCode.String)
            Obj.picture_name = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("picture_name").ToString(), TypeCode.String)
            Obj.folder_path = Utilities.ClearDBNullToMinValue(objDs.Tables("tablePolice").Rows(0).Item("folder_path").ToString(), TypeCode.String)
            outResult.Add(Obj)
            outResult.Add(Obj)
        End If
        Return outResult
    End Function

    Public Function getUserDetailsInOrganizationAll(ByVal inOrganizationID As String) As DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceUsername"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceUsername")

        Return objDs
    End Function

    Protected Function getsUserDetailsInOrganizationAll(ByVal inOrganizationID As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_PoliceUsername"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", inOrganizationID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "PoliceUsername")

        For Each objDr As System.Data.DataRow In objDs.Tables("PoliceUsername").Rows
            Dim obj As User = New User
            obj.citizen_id = objDr.Item("citizen_id")
            obj.police_fullname = objDr.Item("full_name")
            obj.police_age = Utilities.ClearDBNullToMinValue(objDr.Item("age"), TypeCode.Int32)
            obj.username = Utilities.ClearDBNullToEmptyString(objDr.Item("username"))

            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function deleteUser(ByVal inCitizenID As String) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_DELETE_User"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inCitizenID)))
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function insertUser(ByVal inUser As User) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_User"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inCitizenID", _
                Utilities.SendToDatabase(Utilities.ConvertCitizenID(inUser.citizen_id)))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUsername", _
                Utilities.SendToDatabase(inUser.username))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUserPwd", _
                Utilities.SendToDatabase(inUser.user_pwd))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inCreateUser", _
                Utilities.SendToDatabase(inUser.create_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateUser", _
                Utilities.SendToDatabase(inUser.update_user))
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inUpdateDate", _
                Utilities.SendToDatabase(inUser.update_date))
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Sub LoadPoliceUsernameAll(ByVal inOrganizationID As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsUserDetailsInOrganizationAll(inOrganizationID)
    End Sub

    Public Sub LoadUserDetails(ByVal inUserName As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getUserDetails(inUserName)
    End Sub

    Public Overloads Function NextRecord() As Boolean
        If ((Not _Data Is Nothing) AndAlso (((Not _Data Is Nothing) AndAlso _
            (_DataCurrent < Me._Data.Count And _Data.Count > 0)))) Then
            _citizen_id = Me._Data(_DataCurrent).citizen_id
            _police_fullname = Me._Data(_DataCurrent).police_fullname
            _police_age = Me._Data(_DataCurrent).police_age
            _username = Me._Data(_DataCurrent).username

            _DataCurrent += 1
            Return True
        End If
        Return False
    End Function

End Class
